﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
     
namespace BabyShop.DAO
{
    public class Product
    {
        public static DataTable getAll()
        {
            string sql = "select * from products where deleted = 0";
            return DB_Provider.Read(sql);
        }

        public static DataTable find(int id)
        {
            //name = Utils.RemoveUnicode(name, true);
            string sql = "select * from products where id=@id and deleted = 0";
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
            da.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;
            da.Fill(dt);
            return dt;
        }

        public static DataTable find(DBO.Product p)
        {
            //name = Utils.RemoveUnicode(name, true);
            string sql = "select * from products where name like @name and deleted = 0";            
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
            da.SelectCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = "%" + p.name + "%";
            da.Fill(dt);
            return dt;
        }

        public static void Insert(DBO.Product p)
        {
            SqlDataAdapter da = getAdapter();
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);
            DataRow dr = dt.NewRow();
            dr["name"] = p.name;
            dr["category_id"] = p.category_id;
            dr["brand_id"] = p.brand_id;
            dr["image_path"] = p.image_path;
            dr["price"] = p.price;
            dr["quantity"] = p.quantity;
            dr["description"] = p.description;
            dr["import_from"] = p.import_from;
            dr["view_count"] = 0;
            dr["sales"] = p.sales;
            dr["created"] = DateTime.Now.ToString();
            dr["deleted"] = 0;
            dt.Rows.Add(dr);
            da.Update(dt);
        }

        public static void Update(DBO.Product p)
        {
            SqlDataAdapter da = getAdapter();
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);
            DataRow dr = dt.Rows.Find(p.id);
            dr["name"] = p.name;
            dr["category_id"] = p.category_id;
            dr["brand_id"] = p.brand_id;
            if (p.image_path != null)
            {
                dr["image_path"] = p.image_path;
            }
            dr["price"] = p.price;
            dr["quantity"] = p.quantity;
            dr["description"] = p.description;
            dr["import_from"] = p.import_from;
            dr["sales"] = p.sales;                                    
            da.Update(dt);
        }

        public static void Delete(int id)
        {
            SqlDataAdapter da = getAdapter();
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);
            DataRow dr = dt.Rows.Find(id);
            dr["deleted"] = 1;
            da.Update(dt);
        }

        public static SqlDataAdapter getAdapter()
        {
            string sql = "select * from products where deleted = 0";
            return DB_Provider.getAdapter(sql);
        }
    }
}